﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace Pub.DB
{
    public class CSQLite
    {


            public string S_Con;

            int i_ID;
            public string access_con;
            public SQLiteCommand cm;
            public SQLiteConnection cn;
            //public static List<SqlConnection> lst_cn = new List<SqlConnection>();
            //public static List<string> lstConKey = new List<string>();
            //public static List<string> lstConVal = new List<string>();

            public SQLiteDataAdapter da;
            public int ds_count = 0;
            public SQLiteParameter[] pars;
            public List<SQLiteParameter> pars2;
            public string sqlServer_con;
            public string s_sql = "";

            public struct DBInfo
            {
                public string sID;
                public string sServ;
                public string sPass;
                public string sUser;
                public string sD_B_;
                public string sDBCon;
            }

            public List<DBInfo> lstDBInf = new List<DBInfo>();

            public CSQLite(string sDBFile)
            {

                try
                {
                    string sDBCon = "data source=" + sDBFile;
                    cn = new SQLiteConnection(sDBCon);
                }
                catch (Exception ex)
                {
                    CDebug.SendError(ex.ToString());
                }
                //db_init(sDBFile);
            }

            public void db_init(string sDBFile)
            {
                try
                {
                    string sDBCon = "data source=" + sDBFile;
                    cn = new SQLiteConnection(sDBCon);

                    /*
                    DataSet ds1 = sel1("SELECT * FROM T_DBCon ORDER BY [Last] DESC");
                    if (!CDB.dsIsNull(ds1))
                    {
                        for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
                        {
                            DataRow dr1 = ds1.Tables[0].Rows[i];
                            DBInfo dbInf_Tem = new DBInfo();
                            dbInf_Tem.sID = dr1["ID"].ToString();
                            dbInf_Tem.sServ = dr1["Server"].ToString();
                            dbInf_Tem.sD_B_ = dr1["DataBase"].ToString();
                            dbInf_Tem.sUser = dr1["User"].ToString();
                            dbInf_Tem.sPass = dr1["Pass"].ToString();

                            lstDBInf.Add(dbInf_Tem);
                        }
                    }
                    */
                }
                catch (System.Exception ex)
                {
                    CDebug.SendError(ex.ToString());
                }

            }

            public string getDBCon(DBInfo dbInf)
            {
                return "Data Source=" + dbInf.sServ + ";Initial Catalog=" + dbInf.sD_B_.Split(';')[0]
                    + ";Persist Security Info=True;User ID=" + dbInf.sUser + ";Password=" + dbInf.sPass;
            }

            public string getDBCon(DBInfo dbInf, string sD_B_)
            {
                return "Data Source=" + dbInf.sServ + ";Initial Catalog=" + sD_B_
                    + ";Persist Security Info=True;User ID=" + dbInf.sUser + ";Password=" + dbInf.sPass;
            }

            public string getDBCon(string sServ, string sD_B_)
            {
                DBInfo dbInf = new DBInfo();
                dbInf = getDBInfo(sServ);
                return "Data Source=" + dbInf.sServ + ";Initial Catalog=" + sD_B_
                    + ";Persist Security Info=True;User ID=" + dbInf.sUser + ";Password=" + dbInf.sPass;
            }

            public DataSet sel1(string sql)
            {
                try
                {
                    this.s_sql = sql;
                    cn.Open();
                    da = new SQLiteDataAdapter(sql, cn);
                    DataSet ds = new DataSet();

                    da.Fill(ds);
                    SQLiteCommandBuilder sqlCb = new SQLiteCommandBuilder(da);

                    cn.Close();

                    //da.FillSchema(ds, SchemaType.Mapped);
                    try
                    {
                        if (ds == null || ds.Tables[0].Rows.Count == 0)
                        {
                            ds_count = 0;
                        }

                        ds_count = ds.Tables[0].Rows.Count;
                    }
                    catch (System.Exception ex1)
                    {
                        ds_count = 0;
                        CDebug.Send("返回结果个数出错", ex1);
                    }

                    return ds;

                }
                catch (Exception ex)
                {
                    if (cn.State != System.Data.ConnectionState.Closed) cn.Close();

                    CDebug.Send(sql + "; " + ex.ToString());
                    CDebug.Send("sql语句错误", ex);
                    ds_count = 0;
                    return null;
                }
            }

            public int cmd1(string sql)
            {
                cn.Open();
                SQLiteTransaction ts = cn.BeginTransaction();     //使用事务
                cm = new SQLiteCommand();
                cm.Connection = cn;

                cm = new SQLiteCommand(sql, cn);

                int i = 0;
                try
                {
                    i = cm.ExecuteNonQuery();
                }
                catch (System.Exception ex)
                {
                    CDebug.SendError(ex.ToString());
                }
                ts.Commit();        //提交事务
                cn.Close();

                return i;

            }

            /// <summary>
            /// 生成新ID
            /// </summary>
            /// <param name="Tab">表名</param>
            /// <param name="Col">字段名</param>
            /// <returns></returns>
            public int getNewID(string Tab, string Col)
            {

                DataSet ds = sel1("select Max(" + Col + ") + 1 as s1 from " + Tab);
                if (dsIsNull(ds)) return 1;
                if (ds.Tables[0].Rows[0]["s1"].ToString() != null && ds.Tables[0].Rows[0]["s1"].ToString() != "")
                    return Convert.ToInt32(ds.Tables[0].Rows[0]["s1"].ToString());
                else
                    return 1;
            }

            /// <summary>
            /// 判断结果集是否为空
            /// </summary>
            /// <param name="ds"></param>
            /// <returns></returns>
            public Boolean dsIsNull(DataSet ds)
            {
                if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) return true;
                else return false;
            }

            public DBInfo getDBInfo(string sServ)
            {
                DBInfo dbInf = new DBInfo();
                for (int i = 0; i < lstDBInf.Count; i++)
                {
                    if (lstDBInf[i].sServ == sServ)
                    {
                        dbInf = lstDBInf[i];
                        break;
                    }
                }

                return dbInf;
            }

            public List<string> getDBNam(string sServ)
            {
                List<string> lstDBNam = new List<string>();
                DBInfo dbInf = new DBInfo();
                dbInf = getDBInfo(sServ);
                string[] sDBNamS = dbInf.sD_B_.Split(';');
                for (int i = 0; i < sDBNamS.Length; i++)
                {
                    lstDBNam.Add(sDBNamS[i]);
                }

                return lstDBNam;
            }
        
    }
}
